﻿using CallCenter.Data.Base;
using CallCenter.Data.BizUtilities.Base;
using CallCenter.Data.Helper;
using CallCenter.Data.Models;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CallCenter.Data.BizUtilities
{
    public class InvoiceItemsUtility : BaseUtility
    {
        public InvoiceItemsUtility(DataManager manager)
            : base(manager)
        { }

        public ReturnMessage<InvoiceItemsVM> InsertToInvoiceItems(InvoiceItemsVM model)
        {
            var returnMessage = new ReturnMessage<InvoiceItemsVM>();
            //  if(this.ValidateScratchPadModel(model))
            //   {
            if (String.IsNullOrEmpty(model.itemDescription))
                model.itemDescription = "";
            model.totalPrice = (model.unitPrice * model.quantity);
            var id = this.Insert(model.invoiceId, model.unitPrice, model.quantity, model.totalPrice, model.itemDescription);
            if (id == -1)
            {
                returnMessage.Message = "We cannot insert data to database";
                returnMessage.Status = false;
            }
            else
            {
                model.id = id;
                returnMessage.Message = "Record has been Successfully inserted";
                returnMessage.Status = true;
            }
            return returnMessage;
        }

        public ReturnMessage<InvoiceItemsVM> UpdateToInvoiceItems(InvoiceItemsVM model)
        {
            var returnMessage = new ReturnMessage<InvoiceItemsVM>();
            //  if(this.ValidateScratchPadModel(model))
            //   {
            if (String.IsNullOrEmpty(model.itemDescription))
                model.itemDescription = "";
            model.totalPrice = (model.unitPrice * model.quantity);
            var id = this.Update(model.id, model.invoiceId, model.unitPrice, model.quantity, model.totalPrice, model.itemDescription);
            if (id == -1)
            {
                returnMessage.Message = "We cannot insert data to database";
                returnMessage.Status = false;
            }
            else
            {
                model.id = id;
                returnMessage.Message = "Record has been Sucessfully updated to database";
                returnMessage.Status = true;
            }
            return returnMessage;
        }

        //public DataTable GetAll() //Gets all the data form the contacts table
        //{
        //    string sql = "Select id,invoiceId,cast(unitPrice as decimal(18, 3)) as unitPrice,quantity,itemDescription from ddlInvoiceItems";
        //    return this.Factory.ExecuteTable(sql);
        //}


        public List<InvoiceItemsVM> GetAll() //Gets all the data form the contacts table
        {
            string sql = "Select id,cast(invoiceId as int),cast(unitPrice as decimal(18, 3)) as unitPrice, isnull(cast(totalPrice as decimal(18,3)),0) as totalPrice,quantity,itemDescription from ddlInvoiceItems";
            return this.Factory.ExecuteObjects<InvoiceItemsVM>(sql);
        }


        public DataTable getInvoiceItems(int invoiceId) //Gets all the data form the contacts table
        {
            string sql = "Select ii.*, (ii.unitPrice*ii.quantity) as amount from ddlInvoiceItems ii WHERE invoiceId = " + invoiceId;

            return this.Factory.ExecuteTable(sql);
        }

        public int Insert(int invoiceId, decimal unitPrice, int quantity, decimal totalPrice, string itemDescription) //insert data to a Invoice item table
        {
            int nRtn = -1;
            string sql = "INSERT INTO ddlInvoiceItems (invoiceId, unitPrice, quantity, totalPrice, itemDescription) Values(@invoiceId, @unitPrice, @quantity, @totalPrice, @itemDescription) SELECT @pResult =@@IDENTITY";


            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@invoiceId", SqlDbType.Int, 10, invoiceId);
                this.Factory.AddParameter(cmd, "@unitPrice", SqlDbType.Int, 10, unitPrice);
                this.Factory.AddParameter(cmd, "@quantity", SqlDbType.Int, 10, quantity);
                this.Factory.AddParameter(cmd, "@totalPrice", SqlDbType.Int, 10, totalPrice);

                this.Factory.AddParameter(cmd, "@itemDescription", SqlDbType.NVarChar, 100, itemDescription);

                var param = this.Factory.AddParameter(cmd, "@pResult", System.Data.SqlDbType.NVarChar, 500, null);
                param.Direction = System.Data.ParameterDirection.Output;

                this.Factory.ExecuteNonQuery(cmd);
                nRtn = Convert.ToInt32(param.Value);
            }
            return nRtn;






        }
        public int Update(
            int id,
            int invoiceId,
            decimal unitPrice,
            int quantity,
            decimal totalPrice,
            string itemDescription) //insert data to a contact table
        {
            int nRtn = -1;
            string sql = "UPDATE ddlInvoiceItems " +
                 "SET invoiceId=@invoiceId, unitPrice=@unitPrice, quantity=@quantity, totalPrice =@totalPrice, itemDescription=@itemDescription WHERE id=@id";

            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@invoiceId", SqlDbType.Int, 10, invoiceId);
                this.Factory.AddParameter(cmd, "@unitPrice", SqlDbType.Int, 10, unitPrice);
                this.Factory.AddParameter(cmd, "@quantity", SqlDbType.Int, 10, quantity);
                this.Factory.AddParameter(cmd, "@totalPrice", SqlDbType.Int, 10, totalPrice);

                this.Factory.AddParameter(cmd, "@itemDescription", SqlDbType.NVarChar, 100, itemDescription);


                this.Factory.AddParameter(cmd, "@id", SqlDbType.Int, 10, id);
                this.Factory.ExecuteNonQuery(cmd);

            }
            nRtn = id;
            return nRtn; //retruning the data stored in the table
        }


        public void Delete(int nAddressType) //delete the row of data using the id passed to the function
        {
            string sql = "DELETE FROM ddlInvoiceItems " +
                "WHERE ID = @ID ";

            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@ID", SqlDbType.Int, 10, nAddressType);
                this.Factory.ExecuteNonQuery(cmd);
            }


        }

        public void DeleteAll() //delete all the rows in the data.
        {
            string sql = "DELETE FROM ddlInvoiceItems ";

            this.Factory.ExecuteTable(sql);
        }

        public DataTable getPdfItems(int invoiceId) //Gets all the data form the contacts table
        {
            //string sql = "Select ii.*, (ii.unitPrice*ii.quantity) as amount from ddlInvoiceItems ii WHERE invoiceId = " + invoiceId;

          string sql =  " select ii.*, ii.totalPrice as amount, i.term , pr.name as projectname from ddlInvoiceItems ii  " +
               " join ccInvoice i on i.id = ii.invoiceId " +
          " join ddlProductRequests pr on i.productRequestId = pr.id WHERE invoiceId = " + invoiceId;

            return this.Factory.ExecuteTable(sql);
        }

        public List<InvoiceItemsVM> GetAllInvoiceItemByInvoiceId(int id)
        {
            string sql = "Select id,cast(invoiceId as int),cast(unitPrice as decimal(18, 3)) as unitPrice, isnull(cast(totalPrice as decimal(18,3)),0) as totalPrice,quantity,itemDescription from ddlInvoiceItems where invoiceid = " + id;
            return this.Factory.ExecuteObjects<InvoiceItemsVM>(sql);
        }

        public DataTable GetContactsByInvoiceId(int invoiceId) //Gets all the data form the contacts table
        {
            string sql = "select email from ccContacts as cc " +
" join ccInvoiceContact as ci on cc.id = ci.contactId " +
" where email is not null and email != '' and ci.InvoiceId =" + invoiceId;

            return this.Factory.ExecuteTable(sql);
        }
    }
}
